﻿Public Class perCliente
    Inherits perPadrao

#Region "Propriedades publicas"
    Public ReadOnly Property sqlConsulta() As String
        Get
            Dim sSql As String
            sSql = "Select IDCliente, RazaoSocial From OTH_Clientes"
            sSql &= " ORDER BY RazaoSocial"
            Return sSql
        End Get
    End Property
#End Region

#Region "Declaração de variáveis"

    Private objBuscaId As New perProximoId

#End Region

#Region "Métodos"

    Public Function inserirCliente(ByVal sRazaoSocial As String, _
                                  ByVal sNomeFantasia As String, _
                                  ByVal sEndereco As String, _
                                  ByVal sTelefone As String, _
                                  ByVal sEmail As String, _
                                  ByVal sCPF As String, _
                                  ByVal iIdBairro As Int32, _
                                  ByVal iIdCidade As Int32, _
                                  ByVal iIdEstado As Int32, _
                                  ByVal sCEP As String, _
                                  ByVal iIdStatus As Int32, _
                                  ByVal iTpPessoa As Int32, _
                                  ByVal sCNPJ As String, _
                                  ByVal sInsc As String, _
                                  ByVal sObservacoes As String, _
                                  ByVal iIdRegiao As Int32, _
                                  ByVal iIdUsuario As Int32, _
                                  ByVal dtProxContato As Date, _
                                  ByVal iIdRamoAtividade As Int32) As Int32

        Dim iIdCliente As Int32
        Dim sSql As String

        Try
            iIdCliente = Me.objBuscaId.BuscaID("IDCliente", "OTH_Clientes")

            sSql = "  INSERT INTO OTH_Clientes "
            sSql &= " ( "
            sSql &= "   IDCliente, "
            sSql &= "   RazaoSocial, "
            sSql &= "   NomeFantasia, "
            sSql &= "   Endereco, "
            sSql &= "   Telefone, "
            sSql &= "   Email, "
            sSql &= "   IdBairro, "
            sSql &= "   IdEstado, "
            sSql &= "   IdCidade, "
            sSql &= "   CEP, "
            sSql &= "   IdStatus, "
            sSql &= "   TipoPessoa, "
            sSql &= "   CNPJ, "
            sSql &= "   InscEstadual, "
            sSql &= "   Observacoes, "
            sSql &= "   IdUsuarioCad, "
            sSql &= "   IdRegiao, "
            sSql &= "   ProximoContato "
            sSql &= " ) "
            sSql &= " VALUES "
            sSql &= " ( "
            sSql &= iIdCliente & ", "
            sSql &= " ' " & sRazaoSocial & " ', "
            sSql &= " ' " & sNomeFantasia & " ', "
            sSql &= " ' " & sEndereco & " ', "
            sSql &= " ' " & sTelefone & " ', "
            sSql &= " ' " & sEmail & " ', "
            If (iIdBairro > 0) Then
                sSql &= iIdBairro & ", "
            Else
                sSql &= "null, "
            End If
            If (iIdEstado > 0) Then
                sSql &= iIdEstado & ", "
            Else
                sSql &= "null, "
            End If
            If (iIdCidade > 0) Then
                sSql &= iIdCidade & ", "
            Else
                sSql &= "null,"
            End If
            sSql &= " '" & sCEP & "',"
            If (iIdStatus > 0) Then
                sSql &= iIdStatus & ","
            Else
                sSql &= "null, "
            End If

            sSql &= iTpPessoa & ","
            sSql &= "'" & sCNPJ & "',"
            sSql &= "'" & sInsc & "',"
            sSql &= "'" & sObservacoes & "',"
            If (iIdUsuario > 0) Then
                sSql &= iIdUsuario & ","
            Else
                sSql &= "null, "
            End If
            If (iIdRegiao > 0) Then
                sSql &= iIdRegiao & ","
            Else
                sSql &= "null,"
            End If
            sSql &= "'" & dtProxContato & "'"
            sSql &= ") "

            MyBase.executarAcao(sSql)

            Return iIdCliente
        Catch ex As Exception
            Throw New Exception("Exceção no método 'inserirCliente'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function

    Public Sub AlterarCliente(ByVal iIDCliente As Int32, _
                             ByVal sRazaoSocial As String, _
                             ByVal sNomeFantasia As String, _
                             ByVal sEndereco As String, _
                             ByVal sTelefone As String, _
                             ByVal sEmail As String, _
                             ByVal sCPF As String, _
                             ByVal iIdBairro As Int32, _
                             ByVal iIdCidade As Int32, _
                             ByVal iIdEstado As Int32, _
                             ByVal sCEP As String, _
                             ByVal iIdStatus As Int32, _
                             ByVal iTpPessoa As Int32, _
                             ByVal sCNPJ As String, _
                             ByVal sInsc As String, _
                             ByVal sObservacoes As String, _
                             ByVal iIdRegiao As Int32, _
                             ByVal iIdUsuario As Int32, _
                             ByVal dtProxContato As Date, _
                             ByVal iIdRamoAtividade As Int32)

        Dim sSql As String

        Try

            sSql = "  UPDATE OTH_Clientes SET "
            sSql &= "   RazaoSocial = '" & sRazaoSocial & "', "
            sSql &= "   NomeFantasia = '" & sNomeFantasia & "', "
            sSql &= "   Endereco = '" & sEndereco & "', "
            sSql &= "   Telefone = '" & sTelefone & "',"
            sSql &= "   Email = '" & sEmail & "', "
            sSql &= "   CNPJ = '" & sCPF & "', "
            sSql &= "   IDBairro = " & IIf(iIdBairro = 0, "null", iIdBairro) & ", "
            sSql &= "   IDCidade = " & IIf(iIdCidade = 0, "null", iIdCidade) & ", "
            sSql &= "   IDEstado = " & IIf(iIdEstado = 0, "null", iIdEstado) & ", "
            sSql &= "   IDStatus = " & IIf(iIdStatus = 0, "null", iIdStatus) & ", "
            sSql &= "   TipoPessoa = " & iTpPessoa & ", "
            sSql &= "   InscEstadual = '" & sInsc & "', "
            sSql &= "   Observacoes = '" & sObservacoes & "', "
            sSql &= "   IDRegiao = " & IIf(iIdRegiao = 0, "null", iIdRegiao) & ", "
            sSql &= "   IDUsuarioCad = " & IIf(iIdUsuario = 0, "null", iIdUsuario) & " , "
            sSql &= "   ProximoContato = '" & dtProxContato & "' "
            sSql &= " WHERE "
            sSql &= "   IDCliente = " & iIDCliente

            MyBase.executarAcao(sSql)

        Catch ex As Exception
            Throw New Exception("Exceção no método 'alterarPessoa'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Sub

    Public Sub excluirPessoa(ByVal iIdPessoa As Int32)

        Dim sSql As String

        Try
            sSql = "  DELETE FROM "
            sSql &= "   OTH_Clientes "
            sSql &= " WHERE "
            sSql &= "   IDCliente = " & iIdPessoa

            MyBase.executarAcao(sSql)

        Catch ex As Exception
            Throw New Exception("Exceção no método 'excluirPessoa'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Sub

    Public Function selecionarCliente(ByVal iIDCliente As Int32) As DataRow
        Dim sSql As String
        Dim dsDados As New DataSet

        Try
            sSql = " SELECT OTH_Clientes.*,OTH_Status.Descricao "
            sSql &= " FROM OTH_Clientes "
            sSql &= " LEFT JOIN OTH_Status ON OTH_Clientes.idStatus = OTH_Status.idStatus "
            sSql &= " WHERE IDCliente = " & iIDCliente

            dsDados = MyBase.executarConsulta(sSql, "OTH_Clientes")

            If Not dsDados Is Nothing AndAlso dsDados.Tables("OTH_Clientes").Rows.Count > 0 Then
                Return dsDados.Tables("OTH_Clientes").Rows(0)
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw New Exception("Exceção no método 'selecionarCliente'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
    Public Function selecionaCliente(ByVal iIDCliente As Int32, _
                                  ByVal iIDRegiao As Int32, _
                                  ByVal iIDStatus As Int32) As DataTable
        Dim sSql As String
        Dim dsDados As New DataSet

        Try
            sSql = "SELECT "
            sSql &= "   OTH_Clientes.IDCliente, "
            sSql &= "   OTH_Clientes.RazaoSocial, "
            sSql &= "   OTH_Clientes.Endereco, "
            sSql &= "   OTH_Clientes.Telefone, "
            sSql &= "   OTH_Clientes.Email, "
            sSql &= "   OTH_Clientes.CEP, "
            sSql &= "   OTH_Clientes.TipoPessoa, "
            sSql &= "   OTH_Clientes.CNPJ, "
            sSql &= "   OTH_Clientes.InscEstadual, "
            sSql &= "   OTH_Clientes.ProximoContato, "
            sSql &= "   OTH_Bairro.Bairro, "
            sSql &= "   OTH_Cidade.Cidade, "
            sSql &= "   OTH_Estado.Estado, "
            sSql &= "   OTH_Status.Descricao as Status, "
            sSql &= "   OTH_Regiao.Regiao, "
            sSql &= "   OTH_Contatos.IDContato, "
            sSql &= "   OTH_Contatos.IDCliente, "
            sSql &= "   OTH_Contatos.NomeContato, "
            sSql &= "   OTH_Contatos.Telefone as TelefoneContato, "
            sSql &= "   OTH_Contatos.Celular, "
            sSql &= "   OTH_Contatos.Fax, "
            sSql &= "   OTH_Contatos.Email as EmailContato, "
            sSql &= "   OTH_Departamentos.Descricao as Departamento, "
            sSql &= "   COUNT(OTH_Contatos.IDContato) as TotalContato "
            sSql &= "FROM OTH_Clientes "
            sSql &= "   LEFT JOIN OTH_Bairro ON OTH_Bairro.IDBairro = OTH_Clientes.IDBairro "
            sSql &= "   LEFT JOIN OTH_Cidade ON OTH_Clientes.IDCidade = OTH_Cidade.IDCidade "
            sSql &= "   LEFT JOIN OTH_Estado ON OTH_Clientes.IDEstado = OTH_Estado.IDEstado "
            sSql &= "   LEFT JOIN OTH_Status ON OTH_Clientes.IDStatus = OTH_Status.IDStatus "
            sSql &= "   LEFT JOIN OTH_Regiao ON OTH_Clientes.IDRegiao = OTH_Regiao.IDRegiao "
            sSql &= "   LEFT JOIN OTH_Contatos ON OTH_Clientes.IDCliente = OTH_Contatos.IDCliente "
            sSql &= "   LEFT JOIN OTH_Departamentos ON OTH_Contatos.IDDepartamento = OTH_Departamentos.IDDepartamento "

            If iIDCliente > 0 Then
                sSql &= " WHERE OTH_Clientes.IDCliente = " & iIDCliente
            Else
                sSql &= " WHERE OTH_Clientes.IDCliente <> 0 "
            End If

            If iIDRegiao > 0 Then
                sSql &= " AND OTH_Regiao.IDRegiao = " & iIDRegiao
            End If

            If iIDStatus > 0 Then
                sSql &= " AND OTH_Status.IDStatus = " & iIDStatus
            End If

            sSql &= "GROUP BY "
            sSql &= "   OTH_Clientes.IDCliente, "
            sSql &= "   OTH_Clientes.RazaoSocial, "
            sSql &= "   OTH_Clientes.Endereco, "
            sSql &= "   OTH_Clientes.Telefone, "
            sSql &= "   OTH_Clientes.Email, "
            sSql &= "   OTH_Clientes.Cep, "
            sSql &= "   OTH_Clientes.TipoPessoa, "
            sSql &= "   OTH_Clientes.CNPJ, "
            sSql &= "   OTH_Clientes.InscEstadual, "
            sSql &= "   OTH_Clientes.ProximoContato, "
            sSql &= "   OTH_Bairro.Bairro, "
            sSql &= "   OTH_Cidade.Cidade, "
            sSql &= "   OTH_Estado.Estado, "
            sSql &= "   OTH_Status.Descricao, "
            sSql &= "   OTH_Regiao.Regiao, "
            sSql &= "   OTH_Contatos.IDContato, "
            sSql &= "   OTH_Contatos.IDCliente, "
            sSql &= "   OTH_Contatos.NomeContato, "
            sSql &= "   OTH_Contatos.Telefone, "
            sSql &= "   OTH_Contatos.Celular, "
            sSql &= "   OTH_Contatos.Fax, "
            sSql &= "   OTH_Contatos.Email, "
            sSql &= "   OTH_Departamentos.Descricao "

            dsDados = MyBase.executarConsulta(sSql, "OTH_Clientes")

            If Not dsDados Is Nothing AndAlso dsDados.Tables("OTH_Clientes").Rows.Count > 0 Then
                Return dsDados.Tables("OTH_Clientes")
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw New Exception("Exceção no método 'selecionarCliente'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
    Public Function RetornarClientesPorDataContato(ByVal DtBase As Date, ByVal bSomenteAtrasados As Boolean, _
                                                   ByVal iIDCliente As Int32, _
                                                   ByVal iIDStatus As Int32, _
                                                   ByVal iIDContato As Int32) As DataTable
        Dim dsDados As New DataSet
        Dim sSql As String

        Try
            sSql = ""
            sSql &= "   SELECT OTH_Clientes.IDCliente, OTH_Clientes.RazaoSocial, OTH_Clientes.IDStatus, " & vbCrLf
            sSql &= "          OTH_Clientes.ProximoContato, OTH_Contatos.idContato,OTH_Contatos.NomeContato, " & vbCrLf
            sSql &= "          OTH_Status.Descricao, OTH_Status.Tolerancia, OTH_Clientes.IDRegiao " & vbCrLf
            sSql &= "     FROM OTH_Clientes " & vbCrLf
            sSql &= "LEFT JOIN OTH_Contatos ON " & vbCrLf
            sSql &= "          OTH_Clientes.IDCliente =  OTH_Contatos.IDCliente " & vbCrLf
            sSql &= "      AND OTH_Contatos.principal = 1 " & vbCrLf
            sSql &= "LEFT JOIN OTH_Status ON " & vbCrLf
            sSql &= "          OTH_Clientes.IDStatus =  OTH_Status.IDStatus " & vbCrLf
            If (bSomenteAtrasados) Then
                sSql &= "    WHERE OTH_Clientes.ProximoContato < '" & DtBase & "'"
            Else
                sSql &= "    WHERE OTH_Clientes.ProximoContato <= '" & DtBase & "'"
            End If

            If iIDCliente > 0 Then
                sSql &= "       AND OTH_Clientes.IDCliente = " & iIDCliente
            End If
            If iIDStatus > 0 Then
                sSql &= "       AND OTH_Status.IDStatus = " & iIDStatus
            End If
            If iIDContato > 0 Then
                sSql &= "       AND OTH_Contatos.IDContato = " & iIDContato
            End If

            sSql &= "    ORDER BY OTH_Clientes.ProximoContato"

            dsDados = MyBase.executarConsulta(sSql, "OTH_Clientes")

            If Not dsDados Is Nothing AndAlso dsDados.Tables("OTH_Clientes").Rows.Count > 0 Then
                Return dsDados.Tables("OTH_Clientes")
            Else
                Return Nothing
            End If

        Catch ex As Exception

            Throw New Exception("Exceção no método 'RetornarClientesPorDataContato'. " & Environment.NewLine & Environment.NewLine & ex.Message)

        End Try

    End Function
    Public Function selecionaCliente(ByVal iIDCliente As Int32) As DataTable
        Dim sSql As String
        Dim dsDados As New DataSet

        Try
            sSql = " SELECT * FROM OTH_Clientes "
            If iIDCliente > 0 Then
                sSql &= " WHERE IDCliente = " & iIDCliente
            End If

            dsDados = MyBase.executarConsulta(sSql, "OTH_Clientes")

            If Not dsDados Is Nothing AndAlso dsDados.Tables("OTH_Clientes").Rows.Count > 0 Then
                Return dsDados.Tables("OTH_Clientes")
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw New Exception("Exceção no método 'selecionarCliente'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try

    End Function
    Public Function RetornaProximoContato(ByVal idCliente As Int32) As Date
        Try

        Catch ex As Exception
            Throw New Exception("Exceção no método 'RetornaProximoContato'. " & Environment.NewLine & Environment.NewLine & ex.Message)
        End Try
    End Function
    Public Function AtualizaDataProximoContato(ByVal iIDCliente As Int32, ByVal dtProxContato As Date) As Boolean
        Dim sSql As String

        Try
            sSql = "UPDATE OTH_Clientes " & vbCrLf
            sSql &= "  SET ProximoContato = '" & dtProxContato.Date & "'" & vbCrLf
            sSql &= "WHERE IDCliente = " & iIDCliente

            MyBase.executarAcao(sSql)
            Return True

        Catch ex As Exception
            Throw New Exception("Exceção no método 'AtualizaDataProximoContato'. " & Environment.NewLine & Environment.NewLine & ex.Message)
            Return False
        End Try

    End Function

#End Region

End Class
